//
//  PYSqliteManager.swift
//  SwiftDevNote
//
//  Created by jyvyi_ios on 2022/4/26.
//

import Foundation

#if canImport(SQLite)
import SQLite

protocol SqliteTable{
    
    static func tableType() -> Any.Type
    
    static func tableVersion() -> String
    
    static func tableBuilder() -> (TableBuilder) -> Void
}

typealias SqliteTableType = SqliteTable.Type

struct PYSqliteDbManager{
    
    static let DB_BusyTimeout = 5.0  //数据库超时时间
    
    /// DB路径
    /// - Returns: ~
    static func DbPath() -> String{
        
        let path = NSSearchPathForDirectoriesInDomains(
            .documentDirectory, .userDomainMask, true
        ).first!
        let dbName = "db"
        return path + "/" + dbName + ".sqlite3"
    }
    
    /// 打开BD
    /// - Returns: ～
    static func openDb() -> Connection?{
        
        let dbPath = self.DbPath()
        do{
            let db = try Connection(dbPath)
            db.busyTimeout = DB_BusyTimeout
            
            return db
        }catch{
            
        }
        return nil
    }
    
    /// 获取表名
    /// - Parameter type: 类型
    /// - Returns: 表名
    static func tableName(for tableType:SqliteTableType) -> String{
        
        let type = tableType.tableType()
        let version = tableType.tableVersion()
        return String.init(describing: type) + version
    }
    
    /// 建表
    /// - Parameter type: 类型
    static func createTable(for tableType:SqliteTableType) -> Table? {
            
        let tableName = self.tableName(for: tableType)
        let table = Table(tableName)
        let db = self.openDb()
        let createQuery = table.create(temporary: false, ifNotExists: true, withoutRowid: false,block: tableType.tableBuilder())
        do{
            try db?.run(createQuery)
            return table
        }
        catch{
            
        }
        return nil
    }
}

struct SqliteTableManager{
    
    static func table(for table:SqliteTableType) -> Table?{
        
        return PYSqliteDbManager.createTable(for: table)
    }
    static func db() -> Connection?{
        
        return PYSqliteDbManager.openDb()
    }
    static func select(for table:SqliteTableType,
                filter:Expression<Bool>? = nil,
                select:[Expressible],
                order:[Expressible],
                limit:Int? = nil,
                offset:Int? = nil) -> AnySequence<Row>?{
        
        guard let table = self.table(for: table) else {
            return nil
        }
        var query = table.select(select).order(order)
        if let filter = filter {
            query = query.filter(filter)
        }
        if let limit = limit {
            if let offset = offset {
                query = query.limit(limit, offset: offset)
            }
            else{
                query = query.limit(limit)
            }
        }
        do{
            let result : AnySequence<Row>? = try self.db()?.prepare(query)
            return result
        }
        catch{
            debugPrint("查询数据错误",error)
        }
        return nil
    }
    
    @discardableResult static func update(for table:SqliteTableType,filter:Expression<Bool>? = nil,values: Setter...) -> Bool{
        
        guard let table = self.table(for: table) else {
            return false
        }
        var oldObj = table
        if let filter = filter{
            oldObj = table.filter(filter)
        }
        do{
            let query = oldObj.update(values)
            if let count = try self.db()?.run(query){
                debugPrint("修改成功",count)
                return true
            }
            else{
                debugPrint("无数据更新")
            }
        }
        catch{
            debugPrint("修改失败")
        }
        return false
    }
    @discardableResult static func delete(for table:SqliteTableType,filter:Expression<Bool>? = nil) -> Bool{
        
        guard let table = self.table(for: table) else {
            return false
        }
        var query = table.delete()
        if let filter = filter {
            let oldObj = table.filter(filter)
            query = oldObj.delete()
        }
        do{
            if let count = try self.db()?.run(query){
                
                debugPrint("删除成功",count)
                return true
            }
            else{
                debugPrint("无数据删除")
            }
        }catch{
            debugPrint("删除失败",error)
        }
        return false
    }
    
    @discardableResult static func insert(for table:SqliteTableType,values: Setter...) -> Bool{
        
        guard let table = self.table(for: table) else {
            return false
        }
        let query =  table.insert(values)
        do{
            if let rowId = try self.db()?.run(query){
                debugPrint("插入成功",rowId)
                return true
            }
            else{
                debugPrint("插入数据失败")
            }
        }catch{
            debugPrint("插入失败",error)
        }
        return false
    }
}

#endif
